package com.joysuch.wwyt.edu.repository;

import com.joysuch.wwyt.edu.entity.EduCourseStatistics;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @Author ZhangDong
 * @Date 2020/6/24 17:40
 */
public interface EduCourseStatisticsDao extends JpaRepository<EduCourseStatistics, Long>, JpaSpecificationExecutor<EduCourseStatistics> {

    EduCourseStatistics getOneByUserId(Long userId);


    @Query(value = "SELECT * FROM `wwyt_edu_course_statistics` WHERE user_id = ?1 and delete_flag='0' LIMIT 1", nativeQuery = true)
    EduCourseStatistics selectByUserId(Long userId);

    @Override
    @Query("select new EduCourseStatistics(es.courseLearnTime,bu.realName,es.userId) from EduCourseStatistics es " +
            "left join BaseUser bu on es.userId=bu.id where es.courseLearnTime != 0 and bu.deleteFlag = '0' AND (bu.state IS NULL OR bu.state != 44)")
    Page<EduCourseStatistics> findAll(Pageable pageable);

    @Query(value = "SELECT statistics.user_id FROM wwyt_edu_course_statistics statistics left join wwyt_base_user bu on statistics.user_id=bu.id " +
            "where json_contains(statistics.courseware_learned, json_array(?1))=1 and statistics.delete_flag = '0' and bu.delete_flag = '0' AND (bu.state IS NULL OR bu.state != 44)", nativeQuery = true)
    List<Long> countLearnNum(List<Long> ids);

    @Query("SELECT e FROM EduCourseStatistics e where e.userId in (?1) and e.deleteFlag='0' ")
    List<EduCourseStatistics> findCoursesByUserIds(List<Long> userIds);
}
